Solution: Use NUMERIC Data Type

Instead of FLOAT or its siblings, let’s use the NUMERIC or DECIMAL SQL data types for fixed-precision fractional numbers.

Precision and scale#

The NUMERIC and DECIMAL data types store numeric values exactly, up to the precision we specify in the column definition. We can specify precision as an argument to the data type, similar to the syntax we would use for the length of a VARCHAR data type. The precision is the total number of decimal digits we can use in a value in this column. A precision 9 means that we can store a value like 123456789, but we may not be able to store 1234567890.1.

We may also specify a scale in a second argument to the data type. The scale is the number of digits to the right of the decimal point. These digits are included in the precision digits, so a precision of 9 with a scale of 2 means we can store a value like 1234567.89, but not 12345678.91 or 123456.789.

We apply the precision and scale to the column for all rows in the table. In other words, we can’t store values with a scale of 2 in some rows and a scale of 4 in others. It’s ordinary in SQL that a column’s data type applies uniformly on all rows (just as a column defined as VARCHAR(20) would allow a string of that length on every row).

Likewise, if we scale up the value by a billion, you get the expected value:

What should we do if we get a chance to choose the data type we should use for storing decimal values in MySQL? It seems natural to use FLOAT data types in SQL because it shares its name with a data type found in most programming languages. But there is a better choice for the data type.

The data types NUMERIC and DECIMAL behave identically; there should be no difference between the values stored in either data type. DEC is also a synonym for DECIMAL.

We still can’t store values that require infinite precision, such as one-third. But at least we’re more familiar with values that have this restriction in decimal format.

To sum, if we need exact decimal values, we should use the NUMERIC data type. The FLOATdata types in SQL because it shares its name with a data type found in most programming languages. But there is a better choice of data type.